OpenEdge Data Management:<br />SQL Development Transactions and isolation levels

Release 10.1A: OpenEdge Data Management:
SQL Development

Transactions and isolation levels

SQL defines how a database is expected to behave in different situations. These situations are defined as transaction isolation levels. The isolation levels are defined by possible phenomena that might exist in the database at the four possible levels. Before you can understand isolation levels, you must first understand what these phenomena are.

The following phenomena are used to define isolation levels.

Dirty read

A dirty read occurs when one user is updating or inserting a record while a different user is reading it, but the work is not yet committed to the database, as shown in Example 8–1.
Example 8–1: Dirty read
User A executes: 
	INSERT INTO State (state, state_name, region)  
	VALUES (‘ME', 'Maine’, ‘Northeast');  
User B executes: 
	SELECT * FROM State;  
User B sees:  
state ‘ME’  
User A executes:  
	ROLLBACK WORK;  
User B has seen data that really did not exist. 
Nonrepeatable read

A nonrepeatable read occurs when one user is repeating a read operation on the same records but has updated values, as shown in Example 8–2.
Example 8–2: Nonrepeatable read
User A executes:  
SELECT * FROM State;  
User B executes:  
UPDATE State  
SET state_name = 'Arkansas'  
WHERE state = ‘AK’;  
COMMIT WORK;  
SELECT * FROM pub.State  
User A re-executes:  
SELECT * FROM State; 
User A has now updated records in the result set.  
Phantom read

A phantom read occurs when one user is repeating a read operation on the same records, but has new records in the results set, as shown in Example 8–3.
Example 8–3: Phantom read
User A executes:  
	SELECT * FROM State;  
User B executes:  
	INSERT INTO pub.State (state, state_name, region)  
	VALUES (‘CT', 'Connecticut’, ‘Northeast');  
	COMMIT WORK;  
User A re-executes:  
	SELECT * FROM pub.State; 
User 1 has new records in the results set.  